﻿
--获取公司员工档案
CREATE PROCEDURE [dbo].[proc_Employee_GetCompanyList_20151224]
(
	@startIndex nvarchar(20),
	@endIndex nvarchar(20),
	@sort VARCHAR(4),
	@username nvarchar (50),
	@unitId nvarchar(400),            --单位
	@departmentId varchar(8000),   --部门
	@grade nvarchar(4),             --职级 
	@xzjb nvarchar(50),				--行政级别
	@state nvarchar(10),             --状态
	@sex nvarchar(4),		--性别
	@isCore nvarchar(4), --是否核心人员
	@name nvarchar(50)	        --姓名
)	

as

--公司Id
declare @companyid varchar (400)
--设置--
 set @companyid =@unitId
--姓名
--if(@name = '')
--	set @name = '%'
	
DECLARE @sqltj VARCHAR(MAX)--查询条件
--SET @sqltj=' CompanyId is not null and GradeId like '''+@grade+''' and Sex like '''+@sex+''' and EName like ''%'+@name+'%'''
SET @sqltj=' CompanyId is not null '

IF(@companyid<>'%')
SET @sqltj=@sqltj+' and CompanyId in ('+@companyid+')'

IF(@departmentId<>'0')
SET @sqltj=@sqltj+'  and departmentid in ('+@departmentId+')'

if(@grade<>'%')
SET @sqltj=@sqltj+'  and GradeId = '+@grade

if (@xzjb<>'%')
SET @sqltj+=' and XZJB = '''+@xzjb+''''

if(@sex<>'%')
SET @sqltj=@sqltj+'  and Sex = '''+@sex +''''

if (@isCore<>'A' and @isCore<>'')
Set @sqltj=@sqltj +' and IsCore='+@isCore

if(@name <> '')
SET @sqltj=@sqltj+'  and EName like ''%'+@name+'%'''


if(@state = '%')
SET @sqltj=@sqltj+' and State <> 5 '
ELSE SET @sqltj=@sqltj+' and State in ('+@state+') '
	
	declare @sql3 nvarchar (4000)
	begin
	set @sql3 = 
		+ ' declare @indextable table(id int identity(1,1),nid int)'
		+ ' insert into @indextable (nid)'
		+ ' select id '
		+ ' from employee'
				+ ' where'  
				+@sqltj
		+'order by  enumber '+@sort

		+' select' 
		+' E.id'
		+' ,E.UserName'
		+' ,E.EName'
		+' ,E.Enumber'
		+' ,E.ETempNumber'
		+' ,E.Sex'
		+' ,E.CompanyId'
		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
		+' ,E.DepartmentId'
		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'		
		+' ,E.State'
		+' ,E.PostId'
		+' ,(select PostName from Post where id=E.PostId) as PostName'
		+' ,E.GradeId'
		+' ,(select Title from Grade where id=E.GradeId) as GradeName'
		+' ,(select Title from Post_Category where id=E.PostCategory) as PostCategoryName'
		+' ,(select count(0) from Award_Punish where EId=E.id) as Award_Punish'
 		+' ,(select count(0) from @indextable) as coun'
 		+' ,E.WageNumber'
		+' ,E.RuzhiDate'
		+' ,E.LizhiDate'
		+' ,E.Birthday'
		+' ,E.Rtx'
		+' ,E.OfficePhone'
		+' ,E.HomePhone'
		+' ,E.MobilePhone'
		+' ,E.Email'
		+' ,E.IDcard'
		+' ,E.Address'
		+' ,E.Education'
		+' ,E.HeadPic'
		+' ,E.UpName'
		+' ,E.UpTime'
		+' ,E.ZhiWu,E.IsTxlz,E.IsCore,e.GID'	
		+' ,isnull(E.ContractEndDate,''1900-01-01'') as ContractEndDate' --合同到期日期
		+' ,isnull(E.ZhiWuId,''0'') as ZhiWuId' --职务ID
	    +' ,Isnull((Select Title from Employee_ZhiWu where Id=E.ZhiWuId),''调整中'') as ZhiWuTitle' --职务名称'	
	+' from employee E'
	+' inner join @indextable t on '
	+' E.id=t.nid'
	+' where' 
		+' t.id between '+ @startIndex +' and '+ @endIndex 
		
	exec (@sql3)
	--print (@sql3)
	END
	RETURN	







--获取公司员工档案
--ALTER PROCEDURE [dbo].[proc_Employee_GetCompanyList_coun]
--(
--	@startIndex nvarchar(4),
--	@endIndex nvarchar(4),
--	@sort VARCHAR(4),
--	@username nvarchar (50),
--	@unitId nvarchar(4),            --单位
--	@departmentId varchar(8000),   --部门
--	@grade nvarchar(4),             --职级 
--	@state nvarchar(10),             --状态
--	@sex nvarchar(4),		--性别
--	@name nvarchar(50)	        --姓名
--)	

--as

--公司Id
--declare @companyid varchar (4)
--设置--
-- set @companyid =@unitId
--姓名
--if(@name = '')
--	set @name = '%'
	
--DECLARE @sqltj VARCHAR(MAX)--查询条件
--SET @sqltj=' CompanyId like '''+@companyid+''' and GradeId like '''+@grade+''' and Sex like '''+@sex+''' and EName like ''%'+@name+'%'''

--IF(@departmentId<>'0')
--SET @sqltj=@sqltj+'  and departmentid in ('+@departmentId+')'
--if(@state = '%')
--SET @sqltj=@sqltj+' and State <> 5 '
--ELSE
--	SET @sqltj=@sqltj+' and State in ('+@state+') '
	
--	declare @sql3 nvarchar (4000)
--	begin
--	set @sql3 = 
--		+ ' declare @indextable table(id int identity(1,1),nid int)'
--		+ ' insert into @indextable (nid)'
--		+ ' select id '
--		+ ' from employee'
--				+ ' where'  
--				+@sqltj
--		+'order by  enumber '+@sort

--		+' select' 
--		+' E.id'
--		+' ,E.UserName'
--		+' ,E.EName'
--		+' ,E.Enumber'
--		+' ,E.ETempNumber'
--		+' ,E.Sex'
--		+' ,E.CompanyId'
--		+' ,(select companyname from company where id=E.CompanyId) as CompanyName'
--		+' ,E.DepartmentId'
--		+' ,(select dname from Department where id=E.DepartmentId) as DepartmentName'		
--		+' ,E.State'
--		+' ,E.PostId'
--		+' ,(select PostName from Post where id=E.PostId) as PostName'
--		+' ,E.GradeId'
--		+' ,(select Title from Grade where id=E.GradeId) as GradeName'
--		+' ,(select Title from Post_Category where id=E.PostCategory) as PostCategoryName'
--		+' ,(select count(0) from Award_Punish where EId=E.id) as Award_Punish'
-- 		+' ,(select count(0) from @indextable) as coun'
-- 		+' ,E.WageNumber'
--		+' ,E.RuzhiDate'
--		+' ,E.LizhiDate'
--		+' ,E.Birthday'
--		+' ,E.Rtx'
--		+' ,E.OfficePhone'
--		+' ,E.HomePhone'
--		+' ,E.MobilePhone'
--		+' ,E.Email'
--		+' ,E.IDcard'
--		+' ,E.Address'
--		+' ,E.Education'
--		+' ,E.HeadPic'
--		+' ,E.UpName'
--		+' ,E.UpTime'
--		+' ,E.ZhiWu'		
--	+' from employee E'
--	+' inner join @indextable t on '
--	+' E.id=t.nid'
--	+' where' 
--		+' t.id between '+ @startIndex +' and '+ @endIndex 
		
--	exec (@sql3)
--	END
--	RETURN		
